import { Models } from '@/common/typings/modle'
import { command } from '@/server/mysql'
import { ImportException } from '@/core/HttpException'
import ExcelJs from 'exceljs'
import fs from 'fs'
import { valueSetListBySetCode } from '@/api/v1/system/value-set/listBySetCode'
import { ValueSet } from '@/common/typings/valueSet'
import ValueSetItem = ValueSet.ValueSetItem
import { isEmpty, getValidMessage } from '@/common/utils/utils'
import { addInsert, getFields, getFieldValues } from '@/common/utils/sql'

const commonImport = (options: any) => async (ctx: Models.Ctx) => {
  // 接收文件流
  const file: any = ctx.request.files?.file
  if (!file) {
    throw new ImportException({ msg: '没有文件' })
  }
  console.log('开始读取file')
  // file 中的属性 originalFilename filepath mimetype size (单位应该是b)
  const workbook = new ExcelJs.Workbook()
  try {
    await workbook.xlsx.readFile(file.filepath)
  } catch {
    throw new ImportException({ msg: '文件解析失败' })
  }

  console.log('file读取完成，正在解析...')
  // 获取工作簿中的第一个工作表
  const worksheet: any = workbook.getWorksheet(1)
  const validHeaderInfoList = []
  const rowData: any[] = []
  // 遍历工作表的行并打印内容
  worksheet.eachRow({ includeEmpty: true }, function (row: any, rowNumber: any) {
    let cellData = {}
    row.eachCell({ includeEmpty: true }, function (cell: any, colNumber: any) {
      if (rowNumber === 1) {
        const validHeaderInfo = validHeader({ colNumber, value: cell.value, options })
        // 第一行必须满足的条件校验
        if (!validHeaderInfo.result) {
          validHeaderInfoList.push(validHeaderInfo)
        }
      } else {
        // const validRowInfo = validRow({ colNumber, value: cell.value, rowNumber })
        // if (validRowInfo.result) {
        // }
        const colKey = options.excelColumns[colNumber - 1].key
        cellData = {
          ...cellData,
          [colKey]: cell.value,
        }
      }
    })
    if (rowNumber !== 1) {
      rowData.push(cellData)
    }
  })
  console.log('file解析完成，即将删除文件')
  // 读取完成之后将文件删除
  setTimeout(() => {
    fs.unlinkSync(file.filepath)
  })
  if (!rowData.length) {
    throw new ImportException({ msg: '没有可以导入的数据' })
  }
  // 查询人员数据中所有的值集信息
  const { results: valueSetResults } = options.setCodes?.length
    ? await valueSetListBySetCode({ setCodes: options.setCodes })
    : { results: [] }
  // 根据值集的数据验证数据的合法性，并根据值集通过value获取code并存入数据库
  // 对rowData进行验证
  const resultList: any = []
  rowData.forEach((item, index) => {
    const result = validRow({ row: item, index: index + 2, valueSetResults, options })
    resultList.push(...result)
  })
  if (resultList.length) {
    throw new ImportException({ msg: resultList.map((item: any, i: number) => ({ id: i + 1, message: item.message })) })
  }
  const sqlFieldList: any = []
  const resultsInfo: any = { successCount: 0, fieldCount: 0 }
  // 将要导入的数据插入到数据库中
  for (let i = 0; i < rowData.length; i++) {
    const item = rowData[i]
    const sql = addInsert(`INSERT INTO ${options.tableName} (${getFields(item)}) values (${getFieldValues(item)});`)
    try {
      const results = (await command(sql, true)).results
      if (results.fieldCount === 0 && results.affectedRows === 1) {
        // 新增成功
        resultsInfo.successCount++
      } else {
        resultsInfo.fieldCount++
      }
    } catch (e: any) {
      resultsInfo.fieldCount++
      // sql执行的失败原因
      sqlFieldList.push({
        id: i + 1,
        message: e.message,
      })
    }
  }
  if (resultsInfo.successCount > 0) {
    throw new ImportException({
      data: sqlFieldList,
      msg: `导入成功${resultsInfo.successCount}条，导入失败${resultsInfo.fieldCount}条`,
    })
  } else {
    throw new ImportException({
      data: sqlFieldList,
      msg: `导入成功0条，导入失败${resultsInfo.fieldCount}条`,
    })
  }
}
export default commonImport
function validHeader({ colNumber, value, options }: { colNumber: number; value: any; options: any }) {
  const exceptValue = options.excelColumns[colNumber - 1].header
  const result = exceptValue === value
  return {
    result,
    message: result ? '' : `错误信息：第1行第${colNumber}列应该是${exceptValue}，但实际是${value}`,
  }
}
function validRow({
  row,
  index,
  valueSetResults,
  options,
}: {
  row: any
  index: number
  valueSetResults: ValueSetItem[]
  options: any
}) {
  const resultList: any = []
  Object.keys(row).forEach((key) => {
    const colNumber = options.excelColumns.findIndex((item: any) => item.key === key) + 1
    const result = validCol({ row, rowNumber: index, colNumber, key, valueSetResults, options })
    if (!result.result) {
      resultList.push(result)
    }
  })
  return resultList
}

function validCol({
  row,
  rowNumber,
  colNumber,
  key,
  valueSetResults,
  options,
}: {
  row: any
  rowNumber: number
  colNumber: number
  key: string
  valueSetResults: ValueSetItem[]
  options: any
}) {
  const value = row[key]
  // 获取规则
  const rule = options.ruleMap[key]
  if (isEmpty(value)) {
    if (rule.required) {
      return {
        result: false,
        message: getValidMessage(rowNumber, colNumber, {
          errType: 'required',
        }),
      }
    }
    return {
      result: true,
    }
  } else {
    if (rule.type === 'valueSet') {
      // 校验规则为值集
      const valueSetList = valueSetResults.filter((item) => item.setCode === rule.setCode)
      const crtRecord = valueSetList.find((item) => item.name === value)
      if (crtRecord) {
        // 为值集赋值code，一般来讲 valid只干valid的事，但是在这里给值集赋值太方便了，所以就这么干了，应该没有什么问题吧
        row[options.ruleMap[key].valueCodeKey || key.replace(/Name$/, '')] = crtRecord.code
      } else {
        return {
          result: false,
          message: getValidMessage(rowNumber, colNumber, {
            errType: 'valueSet',
            originValue: value,
            setCode: rule.setCode,
            setValues: valueSetList.map((item) => item.name).join(),
          }),
        }
      }
    }
    if (rule.maxLength) {
      if (('' + value).length > rule.maxLength) {
        return {
          result: false,
          message: getValidMessage(rowNumber, colNumber, {
            errType: 'maxLength',
            originValue: value,
            maxLength: rule.maxLength,
          }),
        }
      }
    }
  }
  return {
    result: true,
    message: '',
  }
}
